package br.gov.ana.pulsodagua.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import br.gov.ana.pulsodagua.conection.ConexaoHidro;
import br.gov.ana.pulsodagua.entidades.Chuva;
import br.gov.ana.pulsodagua.entidades.Estacao;

public class ChuvaHidroDAO {
	
	private Connection connection;  
	
    public ChuvaHidroDAO() throws SQLException{  
        this.connection = ConexaoHidro.getConnectionFactory();  
    }  

    public List<Chuva> recuperaTodos() throws SQLException {  
        
        PreparedStatement stmt = this.connection.prepareStatement("SELECT * FROM Chuvas WHERE EstacaoCodigo=01647001 ORDER BY Maxima DESC");  
        ResultSet rs = stmt.executeQuery();  
        List<Chuva> list = new ArrayList<Chuva>();  
          
        while (rs.next()){  
        	Chuva chuva = new Chuva();  
        	chuva.setDataChuva(rs.getDate("Data"));  
        	chuva.setMaxima(rs.getDouble("Maxima"));  
        	chuva.setEstacao(rs.getInt("EstacaoCodigo"));
           list.add(chuva);  
        }  
          
        rs.close();  
        stmt.close();  
        return list;     
     }  
    
    public Date recuperaDataInicial(int estacao) throws SQLException {  
        
        PreparedStatement stmt = this.connection.prepareStatement("SELECT Min(Data) AS dataInicial FROM Chuvas WHERE EstacaoCodigo="+estacao+" AND NivelConsistencia = 1 AND Importado = 0 AND Temporario = 0 AND Removido = 0 AND ImportadoRepetido = 0");  
        ResultSet rs = stmt.executeQuery();  
        
        Date dataIncial = new Date();
        
	        while (rs.next()){  
	        	dataIncial = rs.getDate("dataInicial");
	        }  
          
        rs.close();  
        stmt.close();  
        
        return dataIncial;     
     }  
    
    public List<Chuva> recuperaChuvaPorEstacao(int estacao) throws SQLException {  
        
        PreparedStatement stmt = this.connection.prepareStatement("SELECT * FROM Chuvas WHERE EstacaoCodigo="+estacao+" AND NivelConsistencia = 1 AND Importado = 0 AND Temporario = 0 AND Removido = 0 AND ImportadoRepetido = 0  ORDER BY Data");  
        ResultSet rs = stmt.executeQuery();  
        List<Chuva> list = new ArrayList<Chuva>();  
          
        while (rs.next()){  
        	Chuva chuva = new Chuva();  
        	chuva.setDataChuva(rs.getDate("Data"));  
        	chuva.setMaxima(rs.getDouble("Maxima"));  
        	chuva.setEstacao(rs.getInt("EstacaoCodigo"));
        	chuva.setChuva01(getNullableDouble (rs,"Chuva01"));
        	chuva.setChuva02(getNullableDouble (rs,"Chuva02"));
        	chuva.setChuva03(getNullableDouble (rs,"Chuva03"));
        	chuva.setChuva04(getNullableDouble (rs,"Chuva04"));
        	chuva.setChuva05(getNullableDouble (rs,"Chuva05"));
        	chuva.setChuva06(getNullableDouble (rs,"Chuva06"));
        	chuva.setChuva07(getNullableDouble (rs,"Chuva07"));
        	chuva.setChuva08(getNullableDouble (rs,"Chuva08"));
        	chuva.setChuva09(getNullableDouble (rs,"Chuva09"));
        	chuva.setChuva10(getNullableDouble (rs,"Chuva10"));
        	chuva.setChuva11(getNullableDouble (rs,"Chuva11"));
        	chuva.setChuva12(getNullableDouble (rs,"Chuva12"));
        	chuva.setChuva13(getNullableDouble (rs,"Chuva13"));
        	chuva.setChuva14(getNullableDouble (rs,"Chuva14"));
        	chuva.setChuva15(getNullableDouble (rs,"Chuva15"));
        	chuva.setChuva16(getNullableDouble (rs,"Chuva16"));
        	chuva.setChuva17(getNullableDouble (rs,"Chuva17"));
        	chuva.setChuva18(getNullableDouble (rs,"Chuva18"));
        	chuva.setChuva19(getNullableDouble (rs,"Chuva19"));
        	chuva.setChuva20(getNullableDouble (rs,"Chuva20"));
        	chuva.setChuva21(getNullableDouble (rs,"Chuva21"));
        	chuva.setChuva22(getNullableDouble (rs,"Chuva22"));
        	chuva.setChuva23(getNullableDouble (rs,"Chuva23"));
        	chuva.setChuva24(getNullableDouble (rs,"Chuva24"));
        	chuva.setChuva25(getNullableDouble (rs,"Chuva25"));
        	chuva.setChuva26(getNullableDouble (rs,"Chuva26"));
        	chuva.setChuva27(getNullableDouble (rs,"Chuva27"));
        	chuva.setChuva28(getNullableDouble (rs,"Chuva28"));
        	chuva.setChuva29(getNullableDouble (rs,"Chuva29"));
        	chuva.setChuva30(getNullableDouble (rs,"Chuva30"));
        	chuva.setChuva31(getNullableDouble (rs, "Chuva31")); 
        	
           list.add(chuva);  
        }  
          
        rs.close();  
        stmt.close();  
        return list;     
     }  
    
    public List<Chuva> recuperaChuvaOrdenadaPorData() throws SQLException {  
        
        PreparedStatement stmt = this.connection.prepareStatement("SELECT * FROM Chuvas WHERE EstacaoCodigo=01647001 AND NivelConsistencia = 1 AND Importado = 0 AND Temporario = 0 AND Removido = 0 AND ImportadoRepetido = 0  ORDER BY Data");  
        ResultSet rs = stmt.executeQuery();  
        List<Chuva> list = new ArrayList<Chuva>();  
          
        while (rs.next()){  
        	Chuva chuva = new Chuva();  
        	chuva.setDataChuva(rs.getDate("Data"));  
        	chuva.setMaxima(rs.getDouble("Maxima"));  
        	chuva.setEstacao(rs.getInt("EstacaoCodigo"));
           list.add(chuva);  
        }  
          
        rs.close();  
        stmt.close();  
        return list;     
     }  
    
    public List<Chuva> recuperaChuvaOrdenadaData() throws SQLException {  
        
        PreparedStatement stmt = this.connection.prepareStatement("SELECT * FROM Chuvas WHERE EstacaoCodigo=01647001 AND NivelConsistencia = 1 AND Importado = 0 AND Temporario = 0 AND Removido = 0 AND ImportadoRepetido = 0  ORDER BY Data");  
        ResultSet rs = stmt.executeQuery();  
        List<Chuva> list = new ArrayList<Chuva>();  
          
        while (rs.next()){  
        	Chuva chuva = new Chuva();  
        	chuva.setDataChuva(rs.getDate("Data"));  
        	chuva.setMaxima(rs.getDouble("Maxima"));  
        	chuva.setEstacao(rs.getInt("EstacaoCodigo"));
        	chuva.setChuva01(getNullableDouble (rs,"Chuva01"));
        	chuva.setChuva02(getNullableDouble (rs,"Chuva02"));
        	chuva.setChuva03(getNullableDouble (rs,"Chuva03"));
        	chuva.setChuva04(getNullableDouble (rs,"Chuva04"));
        	chuva.setChuva05(getNullableDouble (rs,"Chuva05"));
        	chuva.setChuva06(getNullableDouble (rs,"Chuva06"));
        	chuva.setChuva07(getNullableDouble (rs,"Chuva07"));
        	chuva.setChuva08(getNullableDouble (rs,"Chuva08"));
        	chuva.setChuva09(getNullableDouble (rs,"Chuva09"));
        	chuva.setChuva10(getNullableDouble (rs,"Chuva10"));
        	chuva.setChuva11(getNullableDouble (rs,"Chuva11"));
        	chuva.setChuva12(getNullableDouble (rs,"Chuva12"));
        	chuva.setChuva13(getNullableDouble (rs,"Chuva13"));
        	chuva.setChuva14(getNullableDouble (rs,"Chuva14"));
        	chuva.setChuva15(getNullableDouble (rs,"Chuva15"));
        	chuva.setChuva16(getNullableDouble (rs,"Chuva16"));
        	chuva.setChuva17(getNullableDouble (rs,"Chuva17"));
        	chuva.setChuva18(getNullableDouble (rs,"Chuva18"));
        	chuva.setChuva19(getNullableDouble (rs,"Chuva19"));
        	chuva.setChuva20(getNullableDouble (rs,"Chuva20"));
        	chuva.setChuva21(getNullableDouble (rs,"Chuva21"));
        	chuva.setChuva22(getNullableDouble (rs,"Chuva22"));
        	chuva.setChuva23(getNullableDouble (rs,"Chuva23"));
        	chuva.setChuva24(getNullableDouble (rs,"Chuva24"));
        	chuva.setChuva25(getNullableDouble (rs,"Chuva25"));
        	chuva.setChuva26(getNullableDouble (rs,"Chuva26"));
        	chuva.setChuva27(getNullableDouble (rs,"Chuva27"));
        	chuva.setChuva28(getNullableDouble (rs,"Chuva28"));
        	chuva.setChuva29(getNullableDouble (rs,"Chuva29"));
        	chuva.setChuva30(getNullableDouble (rs,"Chuva30"));
        	chuva.setChuva31(getNullableDouble (rs, "Chuva31")); 
        	
           list.add(chuva);  
        }  
        rs.close();  
        stmt.close();  
        return list;     
     }  
    
    public static Double getNullableDouble (ResultSet rs, String nomeCampo) throws SQLException {  
    	
        double d = rs.getDouble (nomeCampo);  
        if (rs.wasNull())   
            return null;  
        else  
            return d;  
   }  
    
    public List<Estacao> buscaTodasEstacoes() throws SQLException {  
        
        PreparedStatement stmt = this.connection.prepareStatement("SELECT * FROM Estacao where Codigo=1647001");  
        ResultSet rs = stmt.executeQuery();  
        List<Estacao> list = new ArrayList<Estacao>();  
          
        while (rs.next()){  
        	Estacao estacao = new Estacao();  
        	estacao.setCodigoEstacao(rs.getInt("Codigo")); 
        	estacao.setNome(rs.getString("Nome"));
           list.add(estacao);  
        }  
          
        rs.close();  
        stmt.close();  
        return list;     
     }  
}
